﻿using CRL.Data.DBAccess;
using System;

namespace QueryTest.test
{
    public class union
    {
        #region union
        public static void testUnion()
        {
            //联合查询
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);
            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200);
            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });
            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });
            var union = view1.Union(view2);
            var sql = union.PrintQuery();
            var result = union.ToList();
        }
        public static void testUnion2()
        {
            //联合查询
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);
            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200);
            var view1 = query.Select(b => new unionResult { a1 = b.Id, a2 = b.ProductName });
            var view2 = query2.Select(b => new unionResult { a1 = b.Id, a2 = b.Remark });
            var union = view1.Union(view2);
            var query3 = union.AsQuery();
            query3.Page(10, 1);
            query3.OrderBy(b => b.a1);
            //Console.WriteLine("OrderBy");
            var sql = query3.PrintQuery();
            var result = query3.ToList();
        }

        public static void testUnionJoin()
        {
            var isMsSql = ConfigInit.currentDbType == DBType.MSSQL;
            //联合查询
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);
            if (isMsSql)
            {
                query.Take(10);
            }
            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200);
            if (isMsSql)
            {
                query2.Take(5);
            }
            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });
            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });
            var union = view2.Union(view1);
            var join = query.Join(union, (a, b) => a.Id == b.a1).Select((a, b) => new { a.Id, b.a2 });
            var sql = join.PrintQuery();
            var result = join.ToList();
        }
        public static void testUnionGroup()
        {
            //联合查询
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);
            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200);
            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });
            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });
            var union = view1.Union(view2);
            var q2 = union.AsQuery().GroupBy(b => b.a1).Select(b => new { b.a1 });
            var sql = q2.PrintQuery();
            var result = q2.ToList();
        }
        #endregion


        class unionResult
        {
            public int a1 { get; set; }
            public string a2 { get; set; }
            public string a3 { get; set; }
        }
    }
}
